package db_tool;

import java.io.InputStream;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * 
 * <p>
 * Title: dbBean.java
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2004
 * </p>
 * <p>
 * Company:
 * </p>
 * 
 * @author TanBo
 * @version 1.0
 */
public class DbBean {
	public Connection conn = null; // connection object
	public ResultSet rs = null; // resultset object
	public Statement stmt = null; // statement object
	public PreparedStatement prepstmt = null; // preparedstatement object
	
	
	private static String url = null; // connection parameter:url
	private static String user = null; // connection parameter:user
	private static String password = null; // connection parameter:password
	static {
		try {
			user = DataBaseConfig.getUSER();
			password = DataBaseConfig.getPASSWORD();
			url = DataBaseConfig.getURL();
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			
		} catch (Exception e) {
			System.out.println("dbBean: init error!" + e.toString());
		}
	}
	/**
	 * init()
	 */
	public Connection getConnection() {
		return conn;
	}
	
	public DbBean() {
		try{
			conn = DriverManager.getConnection(url,user,password);
			stmt = conn.createStatement();
			
		} catch (Exception e) {
			System.out.println("dbBean: init error!" + e.toString());
		}
		
	}
	
	public List<String> queryMoniter() throws SQLException{
		List<String> name = new ArrayList<String>();
		String sql = "select username from Account where roleName = 'group'";
		rs = stmt.executeQuery(sql);
		while(rs.next()){
			name.add(rs.getString(1));
		}
		return name;
	}

	/**
	 * @function prepareStatement
	 * @param sql
	 *            String
	 * @throws SQLException
	 */
	public void prepareStatement(String sql) throws SQLException {
		prepstmt = conn.prepareStatement(sql);
	}
	
	public PreparedStatement getPrepareStatement(String sql) throws SQLException {
		prepstmt = conn.prepareStatement(sql);
		return prepstmt;
	}
	/**
	 * @function prepareStatement
	 * @param sql
	 *            String
	 * @param resultSetType
	 *            int
	 * @param resultSetConcurrency
	 *            int
	 * @throws SQLException
	 */
	public void prepareStatement(String sql, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		prepstmt = conn.prepareStatement(sql, resultSetType,
				resultSetConcurrency);
	}

	/**
	 * @function executeQuery
	 * @param sql
	 *            String
	 * @throws SQLException
	 * @return ResultSet
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		if (stmt != null) {
			return stmt.executeQuery(sql);
		} else
			return null;
	}

	/**
	 * @function executeQuery
	 * @throws SQLException
	 * @return ResultSet
	 */
	public ResultSet executeQuery() throws SQLException {
		if (prepstmt != null) {
			return prepstmt.executeQuery();
		} else
			return null;
	}

	/**
	 * @function executeUpdate
	 * @param sql
	 *            String
	 * @throws SQLException
	 */
	public void executeUpdate(String sql) throws SQLException {
		
		if (stmt != null)
			stmt.executeUpdate(sql);
	}
	
	

	public String getMoniter(String group)
	{
		String sql = "select username from Account where superiorName = '"+group+"'";
	    StringBuffer mon = new StringBuffer();
		try {
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
			 mon.append(rs.getString(1)+",");
			}
			if(mon.length() > 0)
			mon.deleteCharAt(mon.length()-1);
		} catch (SQLException e) {
			
			e.printStackTrace();
			return "error";
		}
		return mon.toString();
	}
	
	private List<String> executeSearch(String sql) throws SQLException{
		List<String> list = new ArrayList<String>();
		
		ResultSet rs1 = stmt.executeQuery(sql);
		
		while(rs1.next()){
			StringBuilder buf = new StringBuilder();
			for(int i = 1; i <= rs1.getMetaData().getColumnCount(); i++){
				buf.append(rs1.getString(i)).append(",");
			}
			list.add(buf.toString());
		}
		return list;
		
	}
	
	public Map<String,Map<String,List<String>>> executeGetRequestData(String mon){
		String[] in = {"monitsite","surface","line","dot","water","tool","catches","fish","fishEgg","zooplankton","phytoplankton","benthos","zooplanktonSpecies","phytoplanktonSpecies","benthosSpecies"};
		List<String> index = Arrays.asList(in);
        Map<String,Map<String,List<String>>> map = new HashMap<String,Map<String,List<String>>>();
		Map<String,List<String>> detail = null;
		List<String> list;
		List<String> target = new ArrayList<String>();
		List<String> tableName = new ArrayList<String>();
		String sql = "select targetMoniter,tableName from Agree where requestMoniter = '"+ mon
		             +"' and ((same = '1' and moniterAgree = '1') or (groupAgree = '1' and moniterAgree = '1'))";
    	try {
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				target.add(rs.getString(1));
				tableName.add(rs.getString(2));
			}
			for(int i = 0; i < target.size(); i++){
				String sql2 = null;
				int x = index.indexOf(tableName.get(i).trim());
                String selectId = "(select id from Account where username = '"+target.get(i)+"' or superiorName = '"+target.get(i)+"')";
                switch(x){
				case 0:
					 sql2 = "select * from MonitoringSite where user_id in " +
                             selectId;
					 break;
				case 1:
                    sql2 = "select * from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId+")";
					break;
					
				case 2:
                    sql2 = "select * from MeasuringLine where ID_FractureSurface in " +
                             "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                             "(select InverstigationID from MonitoringSite where user_id in " +
                             selectId + "))";
					 break;
					 
				case 3:
					 sql2 = "select * from MeasuringPoint where ID_MeasuringLine in " +
                             "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                             "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                             "(select InverstigationID from MonitoringSite where user_id in " +
                             selectId+ ")))";
					 break;
				case 4:
					 sql2 = "select * from WaterLayer where ID_MeasuringPoint in " +
                             "(select ID from MeasuringPoint where ID_MeasuringLine in " +
                             "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                             "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                             "(select InverstigationID from MonitoringSite where user_id in " +
                             selectId + "))))";
					 break;
				case 5:
                    sql2 = "select * from CatchTools where SampleID in " +
                            "(select ID_CatchTools from WaterLayer_CatchTools where ID_WaterLayer in " +
                            "(select ID from WaterLayer where ID_MeasuringPoint in " +
                            "(select ID from MeasuringPoint where ID_MeasuringLine in " +
                            "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id IN " +
                            selectId + "))))))";
                    break;
				case 6:
					sql2 = "select * from Catches where ID_WaterLayer in " +
                            "(select ID from WaterLayer where ID_MeasuringPoint in " +
                            "(select ID from MeasuringPoint where ID_MeasuringLine in " +
                            "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId + ")))))";
					break;
				case 7:
					sql2 = "select * from Fishes where ID_Catches in " +
                            "(select SampleID from Catches where ID_WaterLayer in " +
                            "(select ID from WaterLayer where ID_MeasuringPoint in " +
                            "(select ID from MeasuringPoint where ID_MeasuringLine in " +
                            "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId + "))))))";
					break;
				case 8:
					sql2 = "select * from FishEggs where ID_Catches in " +
                            "(select SampleID from Catches where ID_WaterLayer in " +
                            "(select ID from WaterLayer where ID_MeasuringPoint in " +
                            "(select ID from MeasuringPoint where ID_MeasuringLine in " +
                            "(select ID_MeasuringLine from MeasuringLine where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId + "))))))";
					break;
				case 9:
					 sql2 = "select * from Zooplankton where ID_FractureSurface in " +
                             "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                             "(select InverstigationID from MonitoringSite where user_id in " +
                             selectId + "))";
					 break;
				case 10:
					sql2 = "select * from Phytoplankton where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in" +
                            selectId + "))";
					break;
				case 11:
					sql2 = "select * from Benthos where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId + "))";
					break;
				case 12:
					sql2 = "select * from DominantZooplanktonSpecies where ID_Zooplankton in " +
                            "(select SampleID from Zooplankton where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in " +
                            selectId + ")))";
					break;
				case 13:
					sql2 = "select * from DominantPhytoplanktonSpecies where ID_Phytoplankton in " +
                            "(select SampleID from Phytoplankton where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in" +
                            selectId + ")))";
					break;
				case 14:
					sql2 = "select * from DominantBenthosSpecies where ID_Benthos in " +
                            "(select SampleID from Benthos where ID_FractureSurface in " +
                            "(select ID_FractureSurface from FractureSurface where ID_MonitoringSite in " +
                            "(select InverstigationID from MonitoringSite where user_id in" +
                            selectId + ")))";
					break;
				default:
					System.out.println("Error"+x);
				
				}
                System.out.println(sql2);
                detail = map.get(target.get(i));
				if(detail == null){
					detail = new HashMap<String,List<String>>();
					map.put(target.get(i), detail);
				}
				list = executeSearch(sql2);
				detail.put(tableName.get(i),list);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}
	
	public ArrayList<String> executeRequest(String group,String roleName){
		String sql;
		if(roleName.equals("mentor")){
			sql = "select * from Agree where targetMoniter ='"+group+"' and moniterAgree = '0'";
		}else sql = "select * from Agree where (targetGroup = '"+group+"' and groupAgree = '0') or requestGroup = '"+group+"'";
		ArrayList<String> datas = new ArrayList<String>();
		try {
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				StringBuilder param = new StringBuilder();
				for(int i = 1; i <= rs.getMetaData().getColumnCount();i++){
					param.append(rs.getString(i).trim()).append(",");
				}
//				param.append(rs.getString(1)+",");
//				param.append(rs.getString(2)+",");
//				param.append(rs.getString(6));
				datas.add(param.toString());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return datas;
	}
	
	public String executeReplay(List<String> datas ,String[] agree,String agreeName){
		String result = "success";
		  String sql ;
		  for(int i = 0,j=0; i < datas.size(); i++){
			  String[]param = datas.get(i).split(",");
			  if((param[4].trim()).equals("1")&&agreeName.equals("groupAgree")) {
			      continue;
			  }
			  sql = "update Agree set "+agreeName+"= '"+agree[j++]+"' where requestMoniter='"+param[0]+"' and targetMoniter='"+
			       param[1]+"' and tableName = '"+param[5]+"'";			  
			  try {
				stmt.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				return "error";				
			}
			  
		  }
		return result;
	}

	/**
	 * @function executeUpdate
	 * @throws SQLException
	 */
	public void executeUpdate() throws SQLException {
		if (prepstmt != null)
			prepstmt.executeUpdate();
	}

	/**
	 * @function executeUpdate
	 * @throws SQLException
	 */
	public void executeBatch() throws SQLException {
		if (prepstmt != null)
			prepstmt.executeBatch();
	}

	/**
	 * @function addBatch
	 * @param value
	 *            String
	 * @throws SQLException
	 */
	public void addBatch(String value) throws SQLException {
		prepstmt.addBatch(value);
	}

	/**
	 * @function setString
	 * @param index
	 *            int
	 * @param value
	 *            String
	 * @throws SQLException
	 */
	public void setString(int index, String value) throws SQLException {
		prepstmt.setString(index, value);
	}

	/**
	 * @function setInt
	 * @param index
	 *            int
	 * @param value
	 *            int
	 * @throws SQLException
	 */
	public void setInt(int index, int value) throws SQLException {
		prepstmt.setInt(index, value);
	}

	/**
	 * @function setBoolean
	 * @param index
	 *            int
	 * @param value
	 *            boolean
	 * @throws SQLException
	 */
	public void setBoolean(int index, boolean value) throws SQLException {
		prepstmt.setBoolean(index, value);
	}

	/**
	 * @function setDate
	 * @param index
	 *            int
	 * @param value
	 *            Date
	 * @throws SQLException
	 */
	public void setDate(int index, Date value) throws SQLException {
		prepstmt.setDate(index, value);
	}

	/**
	 * @function setLong
	 * @param index
	 *            int
	 * @param value
	 *            long
	 * @throws SQLException
	 */
	public void setLong(int index, long value) throws SQLException {
		prepstmt.setLong(index, value);
	}

	/**
	 * @function setFloat
	 * @param index
	 *            int
	 * @param value
	 *            float
	 * @throws SQLException
	 */
	public void setFloat(int index, float value) throws SQLException {
		prepstmt.setFloat(index, value);
	}

	/**
	 * @function setBytes
	 * @param index
	 *            int
	 * @param value
	 *            byte[]
	 * @throws SQLException
	 */
	public void setBytes(int index, byte[] value) throws SQLException {
		prepstmt.setBytes(index, value);
	}

	/**
	 * @function setBinaryStream
	 * @param index
	 *            int
	 * @param value
	 *            InputStream
	 * @param len
	 *            int
	 * @throws SQLException
	 */
	public void setBinaryStream(int index, InputStream value, int len)
			throws SQLException {
		prepstmt.setBinaryStream(index, value, len);
	}

	/**
	 * @function setTimestamp
	 * @param index
	 *            int
	 * @param timestamp
	 *            Timestamp
	 * @throws SQLException
	 */
	public void setTimestamp(int index, Timestamp timestamp)
			throws SQLException {
		prepstmt.setTimestamp(index, timestamp);
	}

	/**
	 * @function setAutoCommit
	 * @param value
	 *            boolean
	 * @throws SQLException
	 */
	public void setAutoCommit(boolean value) throws SQLException {
		if (this.conn != null)
			this.conn.setAutoCommit(value);
	}

	/**
	 * @function commit
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		this.conn.commit();
	}

	/**
	 * @function rollback
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		this.conn.rollback();
	}

	/**
	 * @function close
	 * @throws Exception
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (Exception e) {
			System.out.println("dbBean close rs error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (Exception e) {
				System.out.println("dbBean close stmt error!");
			} finally {
				try {
					if (prepstmt != null) {
						prepstmt.close();
						prepstmt = null;
					}
				} catch (Exception e) {
					System.out.println("dbBean close prepstmt error!");
				} finally {
					try {
						if (conn != null) {
							conn.close();
							conn = null;
						}
					} catch (Exception e) {
						System.out.println("dbBean close conn error!");
					}
				}
			}
		}
	}

}
